﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data ;
using libDAL;
using System.Windows.Forms;

namespace libCommonTools.BUS
{
    sealed class clsBUS
    {
        private string strTempConStr = @"Data Source=193.9.200.92\SERVERARCHIE;Initial Catalog=ESB_Enterprise;User ID=abc;Password=1234";
        private string strTempConStrgrn = @"Data Source=193.9.200.92\SERVERARCHIE;Initial Catalog=ESB_FMSTransactions;User ID=abc;Password=1234";
        private string strPortID = string.Empty;


        private struct QueryHolder
        {

            public string get_PortAndArea(string strPortID)
            {
                return "select PostCode, AreaDescription " +
                       "from SalesTermSetupInLandCharges " +
                       "where PortID = '" + strPortID + "' " +
                       "GROUP BY PostCode, AreaDescription ";
            }

            public string get_KOP
            {
                get
                {
                    return "select indcode, inddes " +
                           "from mfindividual " +
                           "where catgrocode = 'SEA' AND catcode = 'PACK' ";
                }
            }

            public string get_FindCompany
            {
                // dgvFind.Columns.Add("portid", "Port ID");
                get
                {
                    return "select ltrim(rtrim(comname)) as comname, comname2, comid,comregno, areacode, citycode, " +
                           "poscode, ltrim(rtrim(acccode)) as acccode, ltrim(rtrim(comidHQ)) as comidHQ, portid " +
                           "from mfcompany " +
                           "order by comname asc";
                }

            }

            public string get_FindAddressDetails
            {
                get
                {
                    return "SELECT mfarea.areacode, mfarea.areaname, mfcity.citycode, mfcity.cityname, mfstate.statecode, " +
                           "mfstate.statename, mfcountry.coucode, mfcountry.coudes,mfcountry.regcode " +
                           "FROM mfstate LEFT OUTER JOIN " +
                           "mfcity LEFT OUTER JOIN " +
                           "mfarea ON mfcity.citycode = mfarea.citycode ON mfstate.statecode = mfcity.statecode RIGHT OUTER JOIN " +
                           "mfcountry ON mfstate.coucode = mfcountry.coucode " +
                           "GROUP BY  mfarea.areaname, mfarea.citycode, mfarea.statecode, mfarea.coucode, mfarea.regcode, mfcountry.regcode, " +
                           "mfcountry.coucode, mfcountry.coudes, mfstate.statecode, mfstate.statename, mfcity.citycode, mfcity.cityname, mfarea.areacode " +
                           "ORDER BY  mfarea.regcode ASC , mfarea.coucode ASC, mfarea.statecode ASC, mfarea.citycode ASC, mfarea.areaname ASC ";
                }
            }

            public string get_FindZones
            {
                get
                {
                    return "SELECT discode, disname, description as ZoneDesc " +
                           "FROM mfdistrict " +
                           "order by discode asc, disname asc ";
                }
            }

            public string get_FindPostCode
            {
                get
                {
                    return "SELECT Code, Name " +
                           "FROM MalaysiaPostCodes ";
                }
            }

            public string get_FindPort
            {
                get
                {
                    return "select PortID, PortName from mfport " +
                           "GROUP BY PortID, PortName " +
                           "ORDER BY PortName ASC ";
                }
            }

            public string get_QueryFindGRN(int strTop, string strFieldSearch, string strValue)
            {//
                string strVerb = string.Empty ;

                if (strFieldSearch == "shipname" || strFieldSearch == "comname")
                {
                    strFieldSearch = "mfcompany.comname";
                }
                else
                {
                    strFieldSearch = "tfcargorec."+ strFieldSearch;
                }

                return "SELECT top(" + strTop + ") tfcargorec.connoteno, mfcompany.comname AS shipname,mfcompany_1.comname AS comname, " +
                       "tfcargorec.connotedate, shipinvno, tfcargorec.trastacode , " +
                       "tfcargorec.stickno, tfcargorec.shipid, tfcargorec.conid, tfcargorec.refno, " +
                       "tfcargorec.portid,  tfcargorec.speins, " +
                       "tfcargorec.totqty, tfcargorec.totcbm, tfcargorec.totwei, tfcargorec.issby, " +
                       "tfcargorec.vehplano ,tfcargorec.grntypecode " +
                       "FROM tfcargorec LEFT OUTER JOIN " +
                       "mfcompany AS mfcompany_1 ON tfcargorec.conid = mfcompany_1.comid RIGHT OUTER JOIN " +
                       "mfcompany ON tfcargorec.shipid = mfcompany.comid " +
                       "WHERE " + strFieldSearch + " LIKE '%" + strValue + "%' and (tfcargorec.connoteno is not null or tfcargorec.connoteno <> '')";

              }

            public string get_QueryFindGRN(int strTop, string strFieldSearch, string strValue, string strDateFromTo)
            {//
                string strVerb = string.Empty;
                try
                {
                    if (strFieldSearch == "shipname" || strFieldSearch == "comname")
                    {
                        strFieldSearch = "mfcompany.comname";
                    }
                    else
                    {
                        strFieldSearch = "tfcargorec." + strFieldSearch;
                    }

                    string strDateFrom = strDateFromTo.Substring(0, strDateFromTo.LastIndexOf("_"));
                    string strDateTo = strDateFromTo.Substring(strDateFromTo.LastIndexOf("_") + 1, strDateFromTo.Length - strDateFromTo.LastIndexOf("_") - 1);

                    strVerb = "SELECT top(" + strTop + ") tfcargorec.connoteno, mfcompany.comname AS shipname,mfcompany_1.comname AS comname, " +
                       "tfcargorec.connotedate, shipinvno, tfcargorec.trastacode , " +
                       "tfcargorec.stickno, tfcargorec.shipid, tfcargorec.conid, tfcargorec.refno, " +
                       "tfcargorec.portid,  tfcargorec.speins, " +
                       "tfcargorec.totqty, tfcargorec.totcbm, tfcargorec.totwei, tfcargorec.issby, " +
                       "tfcargorec.vehplano ,tfcargorec.grntypecode " +
                       "FROM tfcargorec LEFT OUTER JOIN " +
                       "mfcompany AS mfcompany_1 ON tfcargorec.conid = mfcompany_1.comid RIGHT OUTER JOIN " +
                       "mfcompany ON tfcargorec.shipid = mfcompany.comid " +
                       "WHERE " + strFieldSearch + " LIKE '%" + strValue + "%' and (tfcargorec.connoteno is not null or tfcargorec.connoteno <> '') and " +
                       "tfcargorec.connotedate >= '" + strDateFrom + "' and tfcargorec.connotedate  <= '" + strDateTo + "' ";
                
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + " get_QueryFindGRN"); 
                }
                return strVerb;
            }

            public string get_CargoGeneralMeasurement
            {
                get
                { 
                    return "select cargocode, cargodes from mfcargo " +
                           " group by cargocode, cargodes ";
                }
            }

            public string get_ItemShipperMeasurement
            {
                get
                {
                    return "select indcode, inddes from mfindividual "+
                           "GROUP BY indcode, inddes ";
                }
            }

            public string get_Areas
            {
                get
                {
                    return "select areacode, areaname from mfarea ";
                }
            }
        }

        private struct VariableHolder
        {
            public DataTable dtQueryResult;
            public cls_DAL _DAL;
            public string strFindType;
            public int intGRNTop;
            public string strFieldSearch;
            public string strValue;
            public string strDateFromTo;
        }

        private VariableHolder varHolder;

        private QueryHolder varQueries;
      
        public string getset_FindType
        {
            get
            { 
                return varHolder.strFindType;
            }
            set
            {
                varHolder.strFindType = value;
            }
        }

        public void get_List(string strType)
        {
                set_FindQuery(strType); 
        }

        public cls_DAL set_DAL
        {
            set
            {
                varHolder._DAL = value;
            }
        }

        public string getset_GRNDateFromTo
        {
            get
            {
                return varHolder.strDateFromTo;
            }
            set
            {
                varHolder.strDateFromTo = value;
            }
        }

        public void set_DALConnection()
        {
            if (getset_FindType != "GRN" 
                && getset_FindType != "GeneralMeasurement"
                 && getset_FindType != "ShipperMeasurement"
                && getset_FindType != "PortArea")
            {
                varHolder._DAL = new cls_DAL(strTempConStr);
            }
            else if (getset_FindType == "GRN" 
                || getset_FindType == "GeneralMeasurement"
                || getset_FindType == "ShipperMeasurement"
                || getset_FindType =="PortArea")
            {
                varHolder._DAL = new cls_DAL(strTempConStrgrn);
            }
        }

        public clsBUS()
        {
            //varHolder = new VariableHolder();
            //varQueries = new QueryHolder();
            
        }

        public string getset_PortAreaID
        {
            get
            {
                return this.strPortID;
            }
            set
            {
                this.strPortID = value;
            }
        }

        public string getset_GRNFeildSearch
        {
            get
            {
                return  varHolder.strFieldSearch;
            }
            set
            {
                varHolder.strFieldSearch = value;
            }
        }

        public int getset_GRNTop
        {
            get 
            {
                return varHolder.intGRNTop;
            }
            set 
            {
                varHolder.intGRNTop = value;
            }
        }

        public string getset_GRNSearchValue
        {
            get
            {
                return varHolder.strValue; 
            }
            set
            {
                varHolder.strValue = value;
            }
        }

        public DataRow[] get_SearhAll()
        {
            DataRow[] drSearched = { };
            try
            {
                if (varHolder.dtQueryResult.Rows.Count > 0)
                {
                    drSearched = varHolder.dtQueryResult.Select();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_SearhAll");
            }
            return drSearched;
        }

        public DataRow[] get_Search(string strQuery)
        {
            DataRow[] drSearched = { };
            try
            {
                if (varHolder.dtQueryResult.Rows.Count > 0)
                {
                    if (strQuery.Trim() != string.Empty)
                    {
                        drSearched = varHolder.dtQueryResult.Select(strQuery);
                    }
                    else
                    {
                        drSearched = varHolder.dtQueryResult.Select();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_Search"); 
            }
            return drSearched;
        }

        public void set_FindQuery(string strFindType)
        {
            try
            {
                if (strFindType == "Company")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_FindCompany);

                }
                else if (strFindType == "AddressDetails")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_FindAddressDetails);  
                }
                else if (strFindType == "Zone")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_FindZones);  
                }
                else if (strFindType == "GRN")
                {
                    varHolder.dtQueryResult = new DataTable();
                    if (getset_GRNDateFromTo == string.Empty || getset_GRNDateFromTo == null)
                    {
                        varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_QueryFindGRN(getset_GRNTop, getset_GRNFeildSearch, getset_GRNSearchValue));
                    }
                    else if(getset_GRNDateFromTo != string.Empty && getset_GRNDateFromTo != null)
                    {
                        varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_QueryFindGRN(getset_GRNTop, getset_GRNFeildSearch, getset_GRNSearchValue,getset_GRNDateFromTo));
                    }
                 }
                else if (strFindType == "Port")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_FindPort);  
                }
                else if (strFindType == "Post")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_FindPostCode);  
                }
                else if (strFindType == "GeneralMeasurement")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_CargoGeneralMeasurement);  
                }
                else if (strFindType == "ShipperMeasurement")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_ItemShipperMeasurement);
                }
                else if (strFindType == "Area")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_Areas);
                }
                else if (strFindType == "PortArea")
                {
                    if (getset_PortAreaID != string.Empty)
                    {
                        varHolder.dtQueryResult = new DataTable();
                        varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_PortAndArea(getset_PortAreaID));
                    }
                }
                else if (strFindType == "KOP")
                {
                    varHolder.dtQueryResult = new DataTable();
                    varHolder.dtQueryResult = varHolder._DAL.get_TableDetail(varQueries.get_KOP);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_FindQuery"); 
            }
        }

        public string get_Description(string strCode, string strType)
        {
           return varHolder._DAL.get_OneKey("", "");  
        }
    }
}
